package ca.paulshin.yunatube.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import ca.paulshin.yunatube.common.Persistent;
import ca.paulshin.yunatube.database.Schema.TableCompetitions;
import ca.paulshin.yunatube.database.Schema.TableGreetings;
import ca.paulshin.yunatube.database.Schema.TableIceShows;
import ca.paulshin.yunatube.database.Schema.TableJukebox;
import ca.paulshin.yunatube.database.Schema.TableKissNCry;
import ca.paulshin.yunatube.database.Schema.TableKnee;
import ca.paulshin.yunatube.database.Schema.TableMuhan;
import ca.paulshin.yunatube.database.Schema.TableMyClips;
import ca.paulshin.yunatube.database.Schema.TableNew;
import ca.paulshin.yunatube.database.Schema.TableOffTheIce;
import ca.paulshin.yunatube.database.Schema.YouTubeColumns;

public class YuNaTubeSqliteDatabase extends YuNaTubeDatabase {

	public static final String DATABASE_NAME = "YunaTube";
	public static final int DATABASE_VERSION = 3;
	public static final String IS_DB_AVAILABLE = "is_db_available";

	private static Context context;

	private static final YuNaTubeSqliteDatabase instance = new YuNaTubeSqliteDatabase();

	public static YuNaTubeSqliteDatabase getInstance() {
		return instance;
	}

	protected SQLiteDatabase getDBConnection() {
		init();
		return db;
	}

	public static void init(Context context) {
		YuNaTubeSqliteDatabase.context = context;
		instance.init();
	}

	@Override
	protected void init() {
		if (db == null || !db.isOpen()) {
			if (helper == null) {
				helper = new SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

					@Override
					public void onCreate(SQLiteDatabase db) {
						db.execSQL(Persistent.CREATE);
						db.execSQL(TableMyClips.CREATE);
						db.execSQL(TableNew.CREATE);
						db.execSQL(TableGreetings.CREATE);
						db.execSQL(TableCompetitions.CREATE);
						db.execSQL(TableIceShows.CREATE);
						db.execSQL(TableOffTheIce.CREATE);
						db.execSQL(TableKissNCry.CREATE);
						db.execSQL(TableJukebox.CREATE);
						db.execSQL(TableMuhan.CREATE);
						db.execSQL(TableKnee.CREATE);
					}

					@Override
					public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
						switch (oldVersion) {
						case 1:
							db.execSQL("DROP TABLE IF EXISTS " + TableMyClips.table);
							db.execSQL(TableMyClips.CREATE);
							db.execSQL(TableNew.CREATE);
							db.execSQL(TableGreetings.CREATE);
							db.execSQL(TableCompetitions.CREATE);
							db.execSQL(TableIceShows.CREATE);
							db.execSQL(TableOffTheIce.CREATE);
							db.execSQL(TableKissNCry.CREATE);
							db.execSQL(TableJukebox.CREATE);
							db.execSQL(TableMuhan.CREATE);
							db.execSQL(TableKnee.CREATE);
						case 2:
							db.execSQL(Persistent.CREATE);
						}
					}
				};
			}

			db = helper.getWritableDatabase();

			db.rawQuery("PRAGMA synchronous = OFF", new String[] {}).close();
			db.rawQuery("PRAGMA journal_mode = MEMORY", new String[] {}).close();
		}
	}

	public long insertEntity(String table, String category, String title, String url) {
		ContentValues values = new ContentValues();
		values.put(YouTubeColumns.category, category);
		values.put(YouTubeColumns.title, title);
		values.put(YouTubeColumns.url, url);

		return insert(table, values);
	}

	public long insertToMyClips(String category, String title, String url) {
		ContentValues values = new ContentValues();
		values.put(TableMyClips.category, category);
		values.put(TableMyClips.title, title);
		values.put(TableMyClips.alias, title);
		values.put(TableMyClips.url, url);

		return insert(TableMyClips.table, values);
	}

	public Cursor fetchCategories(String table) throws SQLException {
		Cursor mCursor = db.query(true, table, new String[] { YouTubeColumns.category }, null, null, null, null, YouTubeColumns._ID, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public String[] fetchRandomClip(String table) throws SQLException {
		int tableSize = 0;
		String[] randomSet = null;
		Cursor mCursor = db.query(table, new String[] { YouTubeColumns.category, YouTubeColumns.title, YouTubeColumns.url }, null, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
			tableSize = mCursor.getCount();
			if (tableSize > 0) {
				int index = (int) (Math.random() * (tableSize - 1));
				mCursor.move(index);
				randomSet = new String[] { mCursor.getString(mCursor.getColumnIndexOrThrow(YouTubeColumns.category)), mCursor.getString(mCursor.getColumnIndexOrThrow(YouTubeColumns.title)),
						mCursor.getString(mCursor.getColumnIndexOrThrow(YouTubeColumns.url)) };
			}
		}
		return randomSet;
	}

	public Cursor fetchMyClips() throws SQLException {
		return db.query(TableMyClips.table, new String[] { TableMyClips._ID, TableMyClips.title, TableMyClips.category, TableMyClips.alias, TableMyClips.url }, null, null, null, null,
				TableMyClips.alias);
	}

	public Cursor fetchNewClips(int limit) throws SQLException {
		return db.query(TableNew.table, new String[] { TableNew._ID, TableNew.title, TableNew.category, TableNew.url }, null, null, null, null, TableNew._ID, String.valueOf(limit));
	}

	public Cursor fetchMyClip(long rowId) throws SQLException {
		Cursor mCursor = db.query(true, TableMyClips.table, new String[] { TableMyClips._ID, TableMyClips.title, TableMyClips.category, TableMyClips.alias, TableMyClips.url }, TableMyClips._ID + "="
				+ rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public Cursor fetchFilteredClips(String query) throws SQLException {
		if (TextUtils.isEmpty(query)) return null;
		
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT _id, title, url FROM ").append(TableGreetings.table).append(" WHERE ").append(YouTubeColumns.title).append(" LIKE '%").append(query).append("%'").append(" UNION ")
		.append("SELECT _id, title, url FROM ").append(TableCompetitions.table).append(" WHERE ").append(YouTubeColumns.title).append(" LIKE '%").append(query).append("%'").append(" UNION ")
		.append("SELECT _id, title, url FROM ").append(TableIceShows.table).append(" WHERE ").append(YouTubeColumns.title).append(" LIKE '%").append(query).append("%'").append(" UNION ")
		.append("SELECT _id, title, url FROM ").append(TableOffTheIce.table).append(" WHERE ").append(YouTubeColumns.title).append(" LIKE '%").append(query).append("%'").append(" UNION ")
		.append("SELECT _id, title, url FROM ").append(TableKissNCry.table).append(" WHERE ").append(YouTubeColumns.title).append(" LIKE '%").append(query).append("%'").append(" UNION ")
		.append("SELECT _id, title, url FROM ").append(TableJukebox.table).append(" WHERE ").append(YouTubeColumns.title).append(" LIKE '%").append(query).append("%'")
		.append(" ORDER BY ").append(YouTubeColumns.title);
		
		return db.rawQuery(sb.toString(), null);
	}
	
	public boolean isInMyClips(String url) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT COUNT(").append(TableMyClips.url).append(")").append(" FROM ").append(TableMyClips.table)
		.append(" WHERE ").append(TableMyClips.url).append("='").append(url).append("'");
		
		Cursor mCount = db.rawQuery(sb.toString(), null);
		if (mCount != null) {
			mCount.moveToFirst();
			int count = mCount.getInt(0);
			mCount.close();
			return count > 0;
		}
		return false;
	}

	public boolean updateMyClip(long rowId, String alias) {
		ContentValues args = new ContentValues();
		args.put(TableMyClips.alias, alias);

		return db.update(TableMyClips.table, args, TableMyClips._ID + "=" + rowId, null) > 0;
	}

	public boolean removeMyClip(String url) {
		return db.delete(TableMyClips.table, TableMyClips.url + "='" + url + "'", null) > 0;
	}

	public boolean deleteMyClip(long rowId) {
		return db.delete(TableMyClips.table, TableMyClips._ID + "=" + rowId, null) > 0;
	}

	/**
	 * Remove all users and groups from database.
	 */
	public void removeAll() {
		db.delete(TableNew.table, null, null);
		db.delete(TableGreetings.table, null, null);
		db.delete(TableCompetitions.table, null, null);
		db.delete(TableIceShows.table, null, null);
		db.delete(TableOffTheIce.table, null, null);
		db.delete(TableKissNCry.table, null, null);
		db.delete(TableJukebox.table, null, null);
		db.delete(TableMuhan.table, null, null);
		db.delete(TableKnee.table, null, null);
	}
}